package com.taobao.askwho.db.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.taobao.askwho.db.base.MysqlRouteBase;
import com.taobao.askwho.po.TagMemory;
import com.taobao.askwho.po.TagRelation;

public class TagInfoDao extends MysqlRouteBase {
	Logger logger = Logger.getLogger(TagInfoDao.class);
	public void addTagRelation(TagRelation tagRelation) throws SQLException {
		String sql = "INSERT INTO tag_relation(UUID,tagname,TYPE,ispublic,related_uuid,user_uuid,createtime,updatetime) VALUE (?,?,?,?,?,?,?,?)";
		int ispublic = 1;
		if(!tagRelation.isPublic())
			ispublic = 0;
		this.execute(sql, new Object[]{tagRelation.getUuid(), tagRelation.getTagName(),tagRelation.getType(),ispublic,
				tagRelation.getRelatedUuid(),tagRelation.getUserUuid(), tagRelation.getCreateTime(),tagRelation.getUpdateTime()});
	}
	
	public void deleteTagRelationByUuid(Set<String> uuidSet) throws SQLException {
		if(uuidSet.size() == 0)
			return;
		
		StringBuilder sb = new StringBuilder("delete from tag_relation where UUID IN (");
		for(int i=0; i<uuidSet.size(); i++) {
			sb.append("?,");			
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(")");		
		this.execute(sb.toString(), uuidSet.toArray());
	}
	
	public void deleteUrlByTagUuid(Set<String> uuidSet) throws SQLException {
		if(uuidSet.size() == 0)
			return;
		StringBuilder sb = new StringBuilder("DELETE FROM url_info WHERE UUID IN (SELECT related_uuid FROM tag_relation WHERE UUID IN (");
		for(int i=0; i<uuidSet.size(); i++) {
			sb.append("?,");			
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append("))");
		this.execute(sb.toString(), uuidSet.toArray());
	}
	
	public void deletePersonByTagUuid(Set<String> uuidSet) throws SQLException {
		if(uuidSet.size() == 0)
			return;
		StringBuilder sb = new StringBuilder("DELETE FROM person_info WHERE UUID IN (SELECT related_uuid FROM tag_relation WHERE UUID IN (");
		for(int i=0; i<uuidSet.size(); i++) {
			sb.append("?,");			
		}
		sb.deleteCharAt(sb.length()-1);
		sb.append("))");
		this.execute(sb.toString(), uuidSet.toArray());
	}	
	
	public List<TagRelation> queryForLuceneIndex(int limit, int size) {
		String sql = "SELECT tagname,uuid FROM tag_relation where ispublic=1 limit ?,?";
		final List<TagRelation> list = new ArrayList<TagRelation>();
		try {
			this.query(sql, new Object[]{limit, size}, new SqlCallBack() {
				@Override
				public void readerRows(ResultSet rs) throws Exception {
					TagRelation rel = new TagRelation();
					rel.setUuid(rs.getString("uuid"));
					rel.setTagName(rs.getString("tagname"));
					list.add(rel);
				}
			});
		} catch (Exception e) {
			logger.error("", e);
		}
		return list;
	}		
	
	public Map<String, Long> queryAllTaqsFromTagRelation(int limit, int size) {
		String sql = "SELECT tagname, COUNT(*) AS mycount FROM tag_relation WHERE ispublic = 1 GROUP BY tagname limit ?,?";
		final Map<String, Long> map = new HashMap<String, Long>();
		try {
			this.query(sql, new Object[]{limit, size}, new SqlCallBack() {
				@Override
				public void readerRows(ResultSet rs) throws Exception {
					map.put(rs.getString("tagname"), rs.getLong("mycount"));
				}
			});
		} catch (Exception e) {
			logger.error("", e);
		}
		return map;
	}	

    public Map<String,Long> queryAllTaqsFromMemory(int limit, int size){
        String sql="select tagname,count from tag_memory limit ?,?";
        final Map<String, Long> map = new HashMap<String, Long>();
        try {
            this.query(sql, new Object[]{limit, size}, new SqlCallBack() {
                @Override
                public void readerRows(ResultSet rs) throws Exception {
                    map.put(rs.getString("tagname"), rs.getLong("count"));
                }
            });
        } catch (Exception e) {
            logger.error("", e);
        }
        return map;
    }

	//查询tag_relation，归并相同的tagname
	public List<TagMemory> listTagRelation() throws Exception {
		final List<TagMemory> list = new ArrayList<TagMemory>();
		String sql = "SELECT tagname ,COUNT(*) AS COUNT FROM tag_relation GROUP BY tagname";
		this.query(sql, new SqlCallBack() {
			@Override
			public void readerRows(ResultSet rs) throws Exception {
				TagMemory po = new TagMemory();
				fillTagMemoryPo(po, rs);
				list.add(po);
			}
		});
		return list;		
	}
	
	/**********************tag_memory操作***********************************/
	
	//添加一个TagMemory到内存表，如果存在则count + 1
	public void addTagMemory(TagMemory po) throws Exception { 
		TagMemory tagPo = queryTagMemory(po.getTagName());
		tagPo.setTagName(po.getTagName());
		tagPo.setCount(po.getCount()+1);
		String sql = "insert into tag_memory(tagname,count) value(?,?)";
		this.execute(sql, new Object[]{tagPo.getTagName(),tagPo.getCount()});	
	}
	
	//不检查，直接插入
	public void addTagMemoryNoCheck(TagMemory po) throws Exception { 
		String sql = "insert into tag_memory(tagname,count,creatTime,UpdateTime) value(?,?,now(),now())";
		this.execute(sql, new Object[]{po.getTagName(),po.getCount()});	
	}
	
	//删除某一个TagMemory的信息，按TagName删除
	public void deleteTagMemoryByTagName(String tagName) throws SQLException {
		String sql = "delete from tag_memory where tagname = ?";
		this.execute(sql, new Object[]{tagName});
	}
	
	public TagMemory queryTagMemory(String tagName) throws Exception {
		final TagMemory po = new TagMemory();
		String sql = "select * from tag_memory where tagname = ?";
		this.query(sql, new Object[] {tagName}, new SqlCallBack() {
			@Override
			public void readerRows(ResultSet rs) throws Exception {
				fillTagMemoryPo(po, rs);
			}
		});
		if(po.getTagName() == null)
			return null;
		return po;
	}
	
	//like查询，返回相似的tag,按count排序
	public List<TagMemory> queryTagMemoryListLike(String queryString, int limit) throws Exception {
		final List<TagMemory> list = new ArrayList<TagMemory>();
		String sql = "select * from tag_memory where tagname like CONCAT(?,?,?) order by count desc limit ?";
		this.query(sql, new Object[] {"%", queryString, "%", limit}, new SqlCallBack() {
			@Override
			public void readerRows(ResultSet rs) throws Exception {
				TagMemory po = new TagMemory();
				fillTagMemoryPo(po, rs);
				list.add(po);
			}
		});
		return list;
	}
	
	private void fillTagMemoryPo(TagMemory po, ResultSet rs) throws SQLException {
		po.setTagName(rs.getString("tagname"));
		po.setCount(rs.getLong("count"));
	}
	
	//删除Memory
	public void deleteTagMemoryAll() throws SQLException {
		String sql = "delete from tag_memory";
		this.execute(sql);
	}


}
